/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package horse_racing_bets_simulator;

import hrbs.dao.DatabaseManager;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.swing.DefaultListModel;

/**
 *
 * @author joshloulou
 */
public class DAO {

    public static String nomUser = "";
    public static String PrenomUser = "";

    public static boolean ConnectApplication(String strLogin, String strPassword) {
        ResultSet result = DatabaseManager.getInstance().select("SELECT login, password FROM client WHERE login = '" + strLogin + "' AND password = '" + strPassword + "'");
        boolean bLoginAuthorized = false;
        try {
            bLoginAuthorized = result.next() ? true : false;
        } catch (Exception e) {
            System.out.println(e);
            e.printStackTrace();
      }
        return bLoginAuthorized;
    }

    public static boolean EnregNewUserInBDD(String login, String pcw, String nom, String prenom, String adresse, String ville, String CP, String telephone, String CBanc, String CJeu) {

        boolean enregUserOK = false;

        boolean insertUserResult = DatabaseManager.getInstance().execute("INSERT INTO client VALUES('" + login + "','" + pcw + "','" + nom + "','" + prenom + "','" + adresse + "','" + ville + "','" + CP + "','" + telephone + "')");

        int newCompteCarteId = DatabaseManager.getInstance().getNewId("CompteCarte", "idCC");
        String reqCompteCarte = "INSERT INTO CompteCarte VALUES (" + newCompteCarteId + ",'" + login + "'," + CBanc + ")";
        boolean insertCompteCarteResult = DatabaseManager.getInstance().execute(reqCompteCarte);

        int newCompteJeuId = DatabaseManager.getInstance().getNewId("CompteJeu", "idCJ");
        String reqCompteJeu = "INSERT INTO CompteJeu VALUES (" + newCompteCarteId + ",'" + login + "'," + CJeu + ")";
        boolean insertCompteCarteJeu = DatabaseManager.getInstance().execute(reqCompteJeu);

        enregUserOK = (insertCompteCarteJeu && insertCompteCarteResult && insertUserResult) ? true : false;
        return enregUserOK;

    }

    public static String RecupNomUser(String login) {
        nomUser = DatabaseManager.getInstance().selectStringColumn("SELECT nomClient FROM client WHERE login = '" + login + "'", "nomClient");
        return nomUser;
    }

    public static String RecupPrenomUser(String login) {
        PrenomUser = DatabaseManager.getInstance().selectStringColumn("SELECT nomClient FROM client WHERE login = '" + login + "'", "prenomClient");
        return PrenomUser;
    }
      

    public static int recupSoldeJeu(String login) {
        return DatabaseManager.getInstance().selectIntegerColumn("SELECT soldeCJ FROM CompteJeu WHERE loginCJClient = '" + login + "'", "soldeCJ");
    }

    public static int recupSoldeBancaire(String login) {
        return DatabaseManager.getInstance().selectIntegerColumn("SELECT soldeCC FROM CompteCarte WHERE loginCJClient = '" + login + "'", "soldeCJ");
    }

    public static DefaultListModel affiche() {

        DefaultListModel model = new DefaultListModel();

        String req = "select nomCheval, nbCourseCouru, nbVictoire, nbPlace from cheval";

        try {

            // Chargement du driver Oracle JDBC
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
            // Connexion à la base
            Connection connx = DriverManager.getConnection //("jdbc:oracle:thin:@192.168.7.7:1521:xe","hr", "hr");
                    ("jdbc:oracle:thin:@10.0.2.15:1521:Insta", "instaUser", "insta");
            System.out.println("Connecté sur l'instance XE avec le schema instaUser");

            // Création d'un objet Oracle DatabaseMetaData
            DatabaseMetaData meta = connx.getMetaData();
            // Renseignement sur le driver
            System.out.println("JDBC driver version is " + meta.getDriverVersion());

            Statement stmt = connx.createStatement();
            ResultSet resultat = stmt.executeQuery(req);

            while (resultat.next()) {
                String tt = "";
                int uu = 0;
                int vv = 0;
                int ww = 0;

                tt = resultat.getString("nomCheval");
                uu = resultat.getInt("nbCourseCouru");
                vv = resultat.getInt("nbVictoire");
                ww = resultat.getInt("nbPlace");

                int coteRecup = Getcote(uu, vv, ww);

                String chaine = tt + " " + uu + "Courses " + vv + "V " + ww + "P " + coteRecup + "/1";

                model.addElement(chaine);

            }

        } catch (Exception c) {
            System.out.println(c);
        }
        return model;
    }

    public static int Getcote(int uu, int vv, int ww) {

        int cote = (100 / uu) * (vv + ww);
        int cote2 = (100 - cote);

        return cote2;

    }

    public static int SoldeCarte(String loginClient) {
       return DatabaseManager.getInstance().selectIntegerColumn("SELECT soldeCC FROM comptecarte WHERE loginCCClient='" + loginClient + "';", "soldeCC");
    }

    public static int SoldeCompte(String loginClient) {
       return DatabaseManager.getInstance().selectIntegerColumn("SELECT soldeCJ FROM comptejeu WHERE loginCCClient='" + loginClient + "';", "soldeCJ");
    }

    public static String RecupPassword(String loginClient, String telClient) {
       return DatabaseManager.getInstance().selectStringColumn("SELECT password FROM client WHERE login='" + loginClient + "' and telephone = '" + telClient + "';", "password");
    }

     public static int getnbVictoire(int idCheval) {
       return DatabaseManager.getInstance().selectIntegerColumn("SELECT nbVictoire FROM cheval WHERE idCheval='" + idCheval + "';", "nbVictoire");
    }

      public static int getnbCoursesCourues( int idCheval) {
       return DatabaseManager.getInstance().selectIntegerColumn("SELECT nbCourseCourue FROM cheval WHERE idCheval='" + idCheval + "';", "nbCourseCourue");
    }

       public static int getnbPlaces( int idCheval) {
       return DatabaseManager.getInstance().selectIntegerColumn("SELECT nbPlace FROM cheval WHERE idCheval='" + idCheval + "';", "nbPlace");
    }

       public static boolean ExecuteurDeRequetes(String req)
       {    
           boolean RequeteInParameter = DatabaseManager.getInstance().execute(req);

           return RequeteInParameter;
       }

       

    

    public static boolean DepotSolde(int sommeTransferee, String loginClient) {
        int soldeCarte = SoldeCarte(loginClient);
        // La maison ne fait pas crédit
        if (sommeTransferee > soldeCarte) {
            // Show error
            return false;
        }
        String reqUpdateCarte = "UPDATE comptecarte SET soldeCC = soldeCJ-" + sommeTransferee + ";";
        String reqUpdateCompte = "UPDATE comptejeu SET soldeCJ = soldeCJ+" + sommeTransferee + ";";
        DatabaseManager.getInstance().execute(reqUpdateCarte);
        DatabaseManager.getInstance().execute(reqUpdateCompte);
        return true;
    }

    public static boolean TransfertSolde(int sommeTransferee, String loginClient) {
        int soldeCompte = SoldeCompte(loginClient);
        // La maison ne fait pas crédit
        if (sommeTransferee > soldeCompte) {
            // Show error
            return false;
        }
        String reqUpdateCarte = "UPDATE comptecarte SET soldeCC = soldeCJ+" + sommeTransferee + ";";
        String reqUpdateCompte = "UPDATE comptejeu SET soldeCJ = soldeCJ-" + sommeTransferee + ";";

        DatabaseManager.getInstance().execute(reqUpdateCarte);
        DatabaseManager.getInstance().execute(reqUpdateCompte);
        return true;
    }

    public static void InsertionParis(String login , String miseJoue, int id) {


        try{
             // Chargement du driver Oracle JDBC
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
            // Connexion à la base
            Connection connx = DriverManager.getConnection //("jdbc:oracle:thin:@192.168.7.7:1521:xe","hr", "hr");
                    ("jdbc:oracle:thin:@10.0.2.15:1521:Insta", "instaUser", "insta");
            System.out.println("Connecté sur l'instance XE avec le schema instaUser");

            // Création d'un objet Oracle DatabaseMetaData
            DatabaseMetaData meta = connx.getMetaData();
            // Renseignement sur le driver
            System.out.println("JDBC driver version is " + meta.getDriverVersion());

            Statement res = connx.createStatement();
            String t = "select MAX(idPari) as id from pari";
            //String update_media = "UPDATE medias SET tag = 1 WHERE id_media= " + id_media + "";
            ResultSet resultat2 = res.executeQuery(t);
            resultat2.next();
            int i = 0;
            try {
                i = resultat2.getInt("id");
            } catch (Exception e) {
                i = 0;
            }
            
            
            String req = "insert into pari VALUES ('" + (i = i + 1) + "'," + miseJoue + ",'" + login + "'," + id + ");";
            res.executeUpdate(req);
            connx.commit();

        }catch(Exception e) {
            System.out.println(e);
        }
        
        
    }
}
